﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_AddEnlistRecord

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select() As DataTable

        Dim sql As New MyOrganizationDataFilter(True)
        Dim dat As New clsData

        sql.AppendFormat("Select a.*,b.OrganizationName from EnlistHeader a ")
        sql.AppendFormat("inner Join organizationview b on a.UOrganizationID=b.UOrganizationID ")
        sql.AppendFormat("where Getdate() between a.ApplyStartDate and a.ApplyEndDate ")


        Return dat.GetData(sql)

    End Function

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select_Volunteer(ByVal UOrganizationID As String, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal EnlistID As Integer, ByVal VOrganizationID As Integer, ByVal trainingWHour As String) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.AppendFormat("SELECT A.VolunteerID,A.VolunteerName,CASE WHEN CD.VolunteerID is null THEN '1' ELSE '0' END as IsEnable, vs.TrainingWHour ")
        sql.AppendFormat(",r.TownName,r.RegionName,oo.VOrganizationName ")
        sql.AppendFormat("from VolunteerDistinctView A ")
        sql.AppendFormat("LEFT JOIN EnlistRecordApplication CD on A.VolunteerId=CD.VolunteerID and CD.EnlistID='{0}' and Status not in ('RW')", EnlistID)

        '基礎和特殊達到時數就可授證
        sql.AppendFormat(" inner join VolunteerStat vs on A.VolunteerID = vs.VolunteerID ")
        sql.AppendFormat(" inner join VBookParameter VP on vs.TrainingBHour>=VP.BasicTrainingHour ")
        sql.AppendFormat(" and vs.TrainingSHour>=VP.SpecialTrainingHour ")

        sql.AppendFormat("LEFT Join Village r on A.VillageID = r.RegionID ")
        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationView vo on vm.VOrganizationID=vo.VOrganizationID ) oo on A.VolunteerID=oo.VolunteerID  ")


        sql.AppendFormat("WHERE A.Active in ('2','Y') ")
        sql.AppendFormat("AND (oo.VOrganizationID = {0} or {0}=0 ) ", VOrganizationID)
        If UOrganizationID <> "0" Then
            sql.AppendFormat("and (oo.DirectUOrganizationID = '{0}'  ) ", UOrganizationID)
        End If
        ' sql.AppendFormat("AND (A.VolunteerID IN (SELECT distinct VolunteerID FROM VOrganizationMapping WHERE VOrganizationID={0}  ) or '{0}' =0 ) ", VOrganizationID)
        'sql.AppendFormat("AND (A.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        'sql.AppendFormat("AND (A.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        sql.AppendFormat("AND (A.VolunteerId = '{0}' or '{0}'='') ", VolunteerId)
        sql.AppendFormat("AND (A.VolunteerName like '%{0}%' or '{0}'='') ", VolunteerName)
        sql.AppendFormat("AND (((A.MobilePhone like '%{0}%' or '{0}'='') or (A.OfficePhone like '%{0}%' or '{0}'=''))) ", TEL)

        If trainingWHour <> "" Then
            sql.AppendFormat("and vs.TrainingWHour <= {0} ", trainingWHour)
        End If

        sql.AppendFormat("ORDER BY IsEnable desc,A.VolunteerID asc")


        Return dat.GetData(sql)

    End Function

  

End Class
